package kr.hkit.powerjava.chapter26.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.powerjava.chapter26.common.DbUtil;
import kr.hkit.powerjava.chapter26.dto.Employee;

public class EmployeeDAO implements DaoInterface<Employee>{
	private Connection connection;
	private PreparedStatement pstmt;
	
	public EmployeeDAO(Connection connection) {
		super();
		this.connection = connection;
	}
	
	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	@Override
	public void insertItem(Employee item) throws SQLException {
		String sql="insert into employee values(?,?,?,?,?,?)";
		pstmt=connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.setString(2, item.getEmpName());
		pstmt.setString(3, item.getTitle());
		pstmt.setInt(4, item.getManager());
		pstmt.setInt(5, item.getSalary());
		pstmt.setInt(6, item.getDno());
		
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
		
	}

	@Override
	public void deleteItemByNo(Employee item) throws SQLException {
		String sql="delect from employee where empNo=?";
		pstmt=connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
		
	}

	@Override
	public void updateItemByNo(Employee item) throws SQLException {
		String sql="update employee set empName=?,title=?,manager=?,salary=?,dno=? where empNo=?";
		pstmt=connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.setString(2, item.getEmpName());
		pstmt.setString(3, item.getTitle());
		pstmt.setInt(4, item.getManager());
		pstmt.setInt(5, item.getSalary());
		pstmt.setInt(6, item.getDno());
		
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
		
	}

	@Override
	public ArrayList<Employee> listItems() throws SQLException {
		ArrayList<Employee> list= new ArrayList<>();
		String sql="select empNo,empName,title,manager,salary,dno from employee";
		pstmt=connection.prepareStatement(sql);
		ResultSet rs= pstmt.executeQuery();
		while(rs.next()){
			list.add(new Employee(rs.getInt("empNo"),
									rs.getString("empName"),
									rs.getString("title"),
									rs.getInt("manager"),
									rs.getInt("salary"),
									rs.getInt("dno")));
		}
		DbUtil.close(rs,pstmt,connection);
		return list;
	}

	@Override
	public Employee searchItem(Employee item) throws SQLException {
		Employee employee=null;
		String sql="select empNo,empName,title,manager,salary,dno from where empNo=?";
		pstmt=connection.prepareStatement(sql);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			employee.setEmpNo(rs.getInt(1));
			employee.setEmpName(rs.getString(2));
			employee.setTitle(rs.getString(3));
			employee.setManager(rs.getInt(4));
			employee.setSalary(rs.getInt(5));
			employee.setDno(rs.getInt(6));
		}
		return employee;
	}

}
